package com.king.base.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.util.*;

/**
 * @author ππ
 * @date 2020-6-10 14:45
 * excel 导出通用类
 * 采用反射生成
 * 目前仅支持导出slx，暂不支持导出xlsx格式
 */

public class ExcelExport<T> {
    Logger logger = LoggerFactory.getLogger(ExcelExport.class);
    private HSSFWorkbook workbook;
    private HSSFSheet sheet;
    private int rowNum;
    private HSSFPatriarch patriarch ;
    private String fileName;
    private int version;

    public ExcelExport(){}
    public ExcelExport(String fileName, int version) {
        this.fileName = fileName;
        this.version = version;
    }

    /**
     * 导出Excel到指定位置
     * @param fields 字段集合  主表key为entity,子表key为children
     * @param dataset 数据集合
     * @param path    文件路径
     */
    public void exportExcel(String title, Map<String,List<String>> fields, Collection<T> dataset, String path){
        createExcelHSSF(title,fields,null,dataset, DateUtils.YYYY_MM_DD,path);
    }

    /**
     * 导出Excel到指定位置
     * @param fields 字段集合 主表key为entity,子表key为children
     * @param header 表头数组
     * @param dataset 数据集合
     * @param path    文件路径
     */
    public void exportExcel(String title,Map<String,List<String>> fields,String[] header,Collection<T> dataset,String path){
        createExcelHSSF(title,fields,header,dataset, DateUtils.YYYY_MM_DD,path);
    }

    /**
     * 导出Excel到指定位置
     * @param fields  字段集合 主表key为entity,子表key为children
     * @param header  表头数组
     * @param dataset 数据集合
     * @param pattern 日期格式
     * @param path    文件路径
     */
    public void exportExcel(String title,Map<String,List<String>> fields,String[] header,Collection<T> dataset,String pattern,String path){
        createExcelHSSF(title,fields,header,dataset,pattern,path);
    }

    /**
     * 导出文件到本地
     * @param fields  字段集合 主表key为entity,子表key为children
     * @param dataset  数据集合
     * @param response  http
     */
    public void exportExcel(String title,Map<String,List<String>> fields, Collection<T> dataset, HttpServletResponse response){
        createExcelHSSF(title,fields,null,dataset, DateUtils.YYYY_MM_DD,response);
    }

    /**
     * 导出文件到本地
     * @param fields  字段集合 主表key为entity,子表key为children
     * @param header  表头数组
     * @param dataset 数据集合
     * @param response http
     */
    public void exportExcel(String title,Map<String,List<String>> fields,String[] header,Collection<T> dataset,HttpServletResponse response){
        createExcelHSSF(title,fields,header,dataset, DateUtils.YYYY_MM_DD,response);
    }

    /**
     * 导出文件到本地
     * @param fields  字段集合 主表key为entity,子表key为children
     * @param header  表头数组
     * @param dataset 数据集合
     * @param pattern 日期格式
     * @param response http
     */
    public void exportExcel(String title,Map<String,List<String>> fields,String[] header,Collection<T> dataset,String pattern,HttpServletResponse response){
        createExcelHSSF(title,fields,header,dataset,pattern,response);
    }
    /**
     * 页面下载excel
     * @param title
     * @param fields
     * @param header
     * @param dataset
     * @param pattern
     * @param response
     */
    private void createExcelHSSF(String title,Map<String,List<String>> fields,String[] header,Collection<T> dataset,String pattern,HttpServletResponse response){
        response.reset(); // 清除buffer缓存
        // 指定下载的文件名
        response.setHeader("Content-Disposition", "attachment;filename=contacts" +(StringUtils.isBlank(fileName)?  DateUtils.dateTimeNow() : fileName) + ".xls");
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        createExcel2003(title,fields,header,dataset,pattern);
        httpExcelHSSF(workbook,response);
    }

    /**
     * 输出到指定路径
     * @param title
     * @param fields
     * @param header
     * @param dataset
     * @param pattern
     * @param path
     */
    private void createExcelHSSF(String title,Map<String,List<String>> fields,String[] header,Collection<T> dataset,String pattern,String path){
        createExcel2003(title,fields,header,dataset,pattern);
        ioExcelHSSF(workbook,path);
    }

    /**
     * 公共方法，创建excel 2003版
     * @param title
     * @param fields
     * @param header
     * @param dataset
     * @param pattern
     */
    private void createExcel2003(String title,Map<String,List<String>> fields,String[] header,Collection<T> dataset,String pattern){
        // 初始化构建
        initWorkBook();
        // 生成样式
        HSSFCellStyle headerStyle = getHeaderStyle(workbook);
        HSSFCellStyle normalStyle = getNormalStyle(workbook);
        // 创建表头
        createTableTitle(title,header.length-1,headerStyle);
        // 生成标题行
        createTableHead(header,headerStyle);
        // 迭代集合
        Iterator it = dataset.iterator();
        // 获取主表属性字段
        List<String> entityFields = fields.get("entity");
        // 获取子表属性字段
        List<String> childFields = fields.get("children");
        // 主表字段长度
        int entityColumnLength = entityFields.size();
        // 合并行
        int rowspan = 0;
        // 每个对象的子表数据
        Object children = null;
        HSSFRow row;
        HSSFCell cell;
        while (it.hasNext()){
            rowNum ++;
            T t = (T) it.next();
            row = sheet.createRow(rowNum);
            // 确定合并行数
            if(childFields !=null && childFields.size() > 0){
                children = getValue(t,"children");
                if(children !=null && ((ArrayList)children).size()>0){
                    rowspan = ((ArrayList)children).size()-1;
                }
            }
            // 主表字段
            for(int i = 0; i <entityFields.size(); i++){
                Object value = getValue(t,entityFields.get(i));
                // 如果需要合并行
                if(rowspan > 0){
                    sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+rowspan,i,i));
                }
                // 创建单元格
                createTableCell(row.createCell(i),value,normalStyle,pattern,rowspan);

            }
            // 子表字段
            if(childFields !=null && childFields.size() > 0){
                if(children !=null ){
                    List list = (ArrayList)children;
                    for(int i = 0;i <list.size(); i++){
                        if(i >0){
                            rowNum++;
                            row = sheet.createRow(rowNum);
                        }
                        for(int j = 0;j<childFields.size();j++){
                            Object value = getValue(list.get(i),childFields.get(j));
                            createTableCell(row.createCell(j +entityColumnLength),value,normalStyle,pattern,rowspan);
                        }
                    }
                }
            }
        }
    }
    /**
     * 初始化构建工作簿
     */
    private void initWorkBook(){
        // 创建一个工作簿
        workbook = HSSFWorkbookFactory.createWorkbook();
        // 创建一个sheet
        sheet = workbook.createSheet();
        // 默认表格列宽
        sheet.setDefaultColumnWidth(15);
        patriarch = sheet.createDrawingPatriarch();
    }
    /**
     * 创建Excel标题
     * @param title 标题
     * @param colspan 合并列
     * @param headerStyle 样式
     */
    private void createTableTitle(String title,int colspan, HSSFCellStyle headerStyle) {
        if(StringUtils.isBlank(title)){
            return;
        }
        HSSFRow row = sheet.createRow(rowNum);
        row.setHeightInPoints(30f);
        HSSFCell cell = row.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,colspan));
        cell.setCellStyle(headerStyle);
        cell.setCellValue(title);
        rowNum ++;
    }
    /**
     * 创建Excel表头
     * @param header
     * @param headerStyle
     */
    private void createTableHead(String[] header, HSSFCellStyle headerStyle) {
        if(header ==null || header.length <1){
            return;
        }
        HSSFRow row = sheet.createRow(rowNum);
        HSSFCell cell;
        for (int i = 0; i < header.length; i++){
            cell = row.createCell(i);
            cell.setCellStyle(headerStyle);
            cell.setCellValue(header[i]);
            cell.setCellType(CellType.STRING);
        }
    }

    /**
     * 创建单元格
     * @param cell
     * @param value
     * @param normalStyle
     */
    private void createTableCell(HSSFCell cell, Object value, HSSFCellStyle normalStyle,String pattern,int rowspan) {
        cell.setCellStyle(normalStyle);
        if (value ==null){
            return;
        }
        if(value instanceof Number){
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(Double.parseDouble(value.toString()));
        //日期
        } else if(value instanceof Date){
            cell.setCellType(CellType.STRING);
            cell.setCellValue(DateUtils.parseDateToStr(pattern,(Date)value));
        // 图片
        } else if(value instanceof byte[]){
            cell.getRow().setHeightInPoints(80);
            sheet.setColumnWidth(cell.getColumnIndex(),(short) (34.5 * 110));
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
                    1023, 255, (short) cell.getColumnIndex(), rowNum, (short) cell.getColumnIndex(), rowNum);
            anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
            patriarch.createPicture(anchor, workbook.addPicture(
                    (byte[])value, HSSFWorkbook.PICTURE_TYPE_JPEG));
        // 全部当作字符串处理
        }else{
            cell.setCellType(CellType.STRING);
            cell.setCellValue(new HSSFRichTextString((String)value));
        }
    }

    /**
     * 创建表头样式
     * @param workbook
     * @return
     */
    private HSSFCellStyle getHeaderStyle(HSSFWorkbook workbook) {
        HSSFCellStyle style = getNormalStyle(workbook);
        style.getFont(workbook).setFontHeightInPoints((short)12);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 字体默认不换行
        style.setWrapText(false);
        return style;
    }

    /**
     * 创建普通样式
     * @param workbook
     * @return
     */
    private HSSFCellStyle getNormalStyle(HSSFWorkbook workbook){
        // 创建字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short)11);
        // 构建样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置边框
//        style.setBorderTop(BorderStyle.THIN);
//        style.setBorderRight(BorderStyle.THIN);
//        style.setBorderBottom(BorderStyle.THIN);
//        style.setBorderLeft(BorderStyle.THIN);
//        style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//        style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//        style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//        style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFont(font);
        // 字体默认换行
        style.setWrapText(true);
        return style;
    }

    /**
     * 反射获取值
     * @param t
     * @param fieldName
     * @param <E>
     * @return
     */
    private <E> Object  getValue(E t,String fieldName){
        String methodName = "get"
                + fieldName.substring(0, 1).toUpperCase()
                + fieldName.substring(1);
        try {
            Method method = t.getClass().getMethod(methodName);
            method.setAccessible(true);
            Object value = method.invoke(t);
            return value;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 输出IO流
     * @param workbook
     * @param path
     * @return
     */
    private void ioExcelHSSF(HSSFWorkbook workbook, String path){
        OutputStream ops =null;
        if(StringUtils.isBlank(fileName)){
            path = path + DateUtils.dateTimeNow() +".xls";
        } else {
            path = path + fileName + ".xls";
        }
        try {
            ops = new FileOutputStream(path);
            workbook.write(ops);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if(ops != null){
                try {
                    ops.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private void httpExcelHSSF(HSSFWorkbook workbook,HttpServletResponse response){
        OutputStream ops = null;
        try {
            ops = response.getOutputStream();
            response.flushBuffer();
            workbook.write(ops);
        } catch (IOException e) {
            e.printStackTrace();
            if(ops !=null){
                try {
                    ops.close();
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }
}
